USE HierarchyIdDemo
GO

-- CREATE a TABLE to store corporate organization
-- hierarchy.  The level for the node is added AS
-- a computed column so that it can be used in an
-- INDEX
CREATE TABLE HierarchyId.Org (
	DepartmentID INT PRIMARY KEY nonclustered,
	DepartmentName VARCHAR(100) NOT NULL,  
	Node hierarchyid NOT NULL,
	Level AS Node.GetLevel() persisted
	-- etc.
)

-- CREATE a "breadth first" INDEX, efficient for SELECTing employees
-- at the same level, e.g., all the employees that directly report
-- to a particular manager
CREATE CLUSTERED INDEX HierarchyId_Org_Breadth_First ON HierarchyId.Org( Level, Node )

-- CREATE a "depth first" INDEX, efficient for SELECTing a sub-tree
-- of nodes, e.g., all employees beneath a particular manager
CREATE unique INDEX HierarchyId_Org_Depth_First ON HierarchyId.Org( Node ) 

-- add the top level using GetRoot() of
-- the HierarchyID data type
INSERT HierarchyId.Org (DepartmentID, Node, DepartmentName)
VALUES (1, hierarchyid::GetRoot(), 'Entire Organization')

-- show the HierarchyId.Org with one top level entry
SELECT Node.ToString() NodeText, DepartmentName FROM HierarchyId.Org
